package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import vo.employeesVO;
import vo.messengerVO;
import db.MySql;

public class employeesDTO {

	private employeesVO evo = null;
	private messengerVO mvo = null;

	private Connection conn = null;

	public employeesDTO() {
		
	}

	public long my_number(String m_id){
		long number = 0;
		conn = new MySql().getConn();
		
		String sql = " SELECT e.e_number "
				+ " FROM semyadmin.messenger m JOIN semyadmin.employees e "
				+ " WHERE e.e_number = m.e_number AND m.m_id = ? ";

		try {

			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m_id);

			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				number = rs.getLong("e_number");
			}
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		return number;
	}
	public employeesVO getInfo(String m_id) {
		conn = new MySql().getConn();
		evo = new employeesVO();

		String sql = " select e.* " + " from semyadmin.employees e join "
				+ " (select e_number " + " from semyadmin.messenger "
				+ " where m_id = ? )a " + " where e.e_number = a.e_number ";

		try {

			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, m_id);

			ResultSet rs = pst.executeQuery();

			while (rs.next()) {
				evo.setE_number(rs.getLong("e_number"));
				evo.setE_name(rs.getString("e_name"));
				evo.setE_birthday(rs.getInt("e_birthday"));
				evo.setE_email(rs.getString("e_email"));
				evo.setE_contact(rs.getInt("e_contact"));
				evo.setE_emergency(rs.getInt("e_emergency"));
				evo.setE_address(rs.getString("e_address"));
				evo.setE_position(rs.getString("e_position"));
				evo.setE_duty(rs.getString("e_duty"));
				evo.setD_code(rs.getInt("d_code"));
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		}

		return evo;
	}
	
	public String getTeamName(int d_code){
		conn = new MySql().getConn();
		String teamName = "";
		
		String sql = "select d_tname "
				+ " from semyadmin.departments "
				+ " where d_code = ? ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, d_code);

			ResultSet rs = pst.executeQuery();
			
			while (rs.next()) {
				teamName = rs.getString("d_tname");
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		} 
		
		return teamName;
	}
	
}
